package itheima01;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import utils.C3P0Util;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;

/**
 * @author xxx
 * @date 2021/1/7 21:13
 * @description
 */
public class TestDemo {
    @Test
    public  void  test() throws SQLException {
        Connection connection= C3P0Util.getConnection();
        Statement statement=connection.createStatement();

        String sql="update account set money=money-1000 where id='2'";
        String sql1="update account set money=money+1000 where id='1'";

        int a=statement.executeUpdate(sql);
        if(a>0){
            System.out.println("tom账户成功扣款1000元~~~");
        }else {
            System.out.println("tom账户失败扣款1000元~~~");
        }

        System.out.println(1/0);

        int b=statement.executeUpdate(sql1);
        if(b>0){
            System.out.println("jerry账户收款1000元成功~~~");
        }else {
            System.out.println("jerry账户收款1000元失败~~~");
        }

        C3P0Util.release(connection,statement,null);

    }

    @Test
    public void test1(){
        Connection connection=null;
        Statement statement=null;

        try {
            connection=C3P0Util.getConnection();
            //开启事务
            connection.setAutoCommit(false);
            statement=connection.createStatement();

            String sql="update account set money=money-1000 where id='2'";
            String sql1="update account set money=money+1000 where id='1'";

            int a=statement.executeUpdate(sql);
            int b=statement.executeUpdate(sql1);

            System.out.println(1/0);


            if(a>0){
                System.out.println("tom账户成功扣款1000元~~~");
            }else {
                System.out.println("tom账户失败扣款1000元~~~");
            }

            if(b>0){
                System.out.println("jerry账户收款1000元成功~~~");
            }else {
                System.out.println("jerry账户收款1000元失败~~~");
            }
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            if(connection !=null){
               try {
                   connection.rollback();
               } catch (SQLException e1) {
                   e1.printStackTrace();
               }
            }
        }finally {
            C3P0Util.release(connection,statement,null);
        }
    }
    @Test
    public  void Test(){
        //设置连接对象
        Connection connection=null;
        try {
            QueryRunner queryRunner=new QueryRunner();
            //开启事务
            connection=C3P0Util.getConnection();
            connection.setAutoCommit(false);

           //update account set money=money-? where name=?
            String a="update account set money =money-? where name=?";
            String b="update account set money =money+? where name=?";

            int t=queryRunner.update(connection,a,1000,"tom");

            System.out.println(1/0);

            int q=queryRunner.update(connection,a,1000,"jerry");
            if(t>0){
                System.out.println("tom账户成功扣款1000元~~~");
            }else {
                System.out.println("tom账户扣款1000元失败~~~");
            }
            if(q>0) {
                System.out.println("jerry账户收款1000元成功~~~");
            } else {
                System.out.println("jerry账户收款1000元失败~~~");
            }
            connection.commit();
        }catch (SQLException e) {
            e.printStackTrace();
            if (connection!=null){
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            }
        }

        C3P0Util.release(connection,null,null);
        //使用jar包DButil创建QueryRunner对象
        //2.获取连接对象
        //3.连接对象开启事务
        //定义2条sql语句(2条update语句:扣款,收款)
        //使用jar包DButil的工具类QueryRunner进行扣款
        //操作报错
        //使用jar包DButil的工具类QueryRunner进行收款
        //结束事务
        //报错事务回滚
        //关闭流
    }
    @Test
    public  void  Testwo() throws SQLException {

        Connection connection=null;
        try {
            //1.空参构造创建QueryRunner对象
            QueryRunner queryRunner=new QueryRunner();
            //2.获取连接对象
            connection= C3P0Util.getConnection();
            //3.连接对象开启事务
            connection.setAutoCommit(false);
            //定义2条sql语句(2条update语句:扣款,收款)
            String a="update account set money=money-? where name=?";
            String b="update account set money=money+? where name=?";
            //4.QueryRunner对象执行sql语句获取结果
            int c=queryRunner.update(connection,a,1000,"tom");
            //出异常了
            System.out.println(1/0);
            //5.sql语句正常执行,提交事务,处理结果
            int d=queryRunner.update(connection,b,1000,"jerry");

            DbUtils.commitAndCloseQuietly(connection);

            if(c>0) {
                System.out.println("tom账户成功扣款1000元~~~");
            } else {
                System.out.println("tom账户扣款1000元失败~~~");
            }

            if(d>0) {
                System.out.println("jerry账户收款1000元成功~~~");
            } else {
                System.out.println("jerry账户收款1000元失败~~~");
            }
        }catch (Exception e){
           e.printStackTrace();
           DbUtils.rollbackAndCloseQuietly(connection);
        }
    }
}
